<?php


class Coworker_data extends CI_Model 
{

	function __construct()
	{
		parent::__construct();
	}
	
	
	//SELECT FUNCTIONS
	public function select_jobcluster()
	{
		$result = array();
		$sql = "SELECT * FROM `jobcluster`";
		
		$query = $this->db->query($sql);
		
		foreach ($query->row() as $row)
		{
			$result[] = $row;
		}
		
		return $result;
	}
		
	 public function select_expertgroups()
	{	
		$result_expertgroup = array();
		$sql = "SELECT * FROM `expertgroup`";
		
		$query = $this->db->query($sql);
		
		foreach ($query->row() as $row)
		{
			$result_expertgroup[] = $row;
		}
		
		return $result_expertgroup;
	}
	
	public function select_teams()
	{
		$result_teams = array();
		$sql = "SELECT * FROM `team`";
		
		$query = $this->db->query($sql);
		
		foreach ($query->row() as $row)
		{
			$result_teams[] = $row;
		}
		
		return $result_teams;
	}
	
	public function select_tags()
	{
		$result = array();
		$sql = "SELECT * FROM `tag`";
		
		$query = $this->db->query($sql);
	  
		foreach ($query->row() as $row)
		{
			$result[] = $row;
		}
		
		return $result;
	}
		
	
	
	public function insert_jobcluster($jobcluster_arr)
	{
	
		$sql = "INSERT IGNORE INTO `jobcluster`
				SET `name` = ?";
		
		foreach($jobcluster_arr as $jobcluster)
		{
			$this->db->query($sql,array($jobcluster));
		}
	}
	
	public function insert_expertgroups($expertgroup_arr)	
	{
		$sql = "INSERT IGNORE INTO `expertgroup`
				SET `name` = ?";
				
		foreach($expertgroup_arr as $expertgroup)
		{
			$this->db->query($sql,array($expertgroup));
		}
	}
	
	public function insert_locations($location_arr)
	{
		$sql = 'SELECT `city`
				FROM `place`
				WHERE `street` IS NULL
				AND		`streetnr`	IS NULL
				AND		`postalcode`	IS NULL
				AND		`code`IS NULL
				AND 	`room`	IS NULL';
		
		
		
		$existing_locations = NULL;
		if($query = $this->db->query($sql))
		{
			foreach($query->result() as $row)
			{
				$existing_locations[]	= $row->city;
			}
		}
		
		$sql = 'INSERT INTO `place`
				SET `city` = ?';
		
		foreach($location_arr as $location)
		{
			if(isset($existing_locations))
			{
				if(FALSE === array_search($location, $existing_locations))
				{
					$this->db->query($sql,array($location));
				}
			}
			else
			{
				$this->db->query($sql,array($location));
			}
		}
		
	}
	
	public function select_cost_center()
	{
		$result_cost_center = array();
		$sql = "SELECT * FROM `costcenter`";
		
		$query = $this->db->query($sql);
				   
		foreach ($query->row() as $row)
		{
			$result[] = $row;
		}
		
		return $result;
	}
	
	public function insert_cost_center($cost_center_arr)
	{
		$sql = 'INSERT IGNORE INTO `costcenter`
				SET `id` = ?';
		
		foreach($cost_center_arr as $cost_center)
		{
			$this->db->query($sql,array($cost_center));
		}
	}
	
	public function select_contact_types()
	{
		$result_contact_types = array();
		$sql = "SELECT * FROM `contacttype`";
		
		$query = $this->db->query($sql);
				   
		foreach ($query->row() as $row)
		{
			$result[] = $row;
		}
		
		return $result;
	}
	
	public function select_departments()
	{
		$result_departments = array();
		$sql = "SELECT * FROM `department`";
		
		$query = $this->db->query($sql);
				   
		foreach ($query->row() as $row)
		{
			$result[] = $row;
		}
		
		return $result;
	}
	
	public function insert_contact_types($contact_type_arr)
	{
		$sql = 'INSERT IGNORE INTO `contacttype`
				SET `name` = ?';
		
		foreach($contact_type_arr as $contact_type)
		{
			$this->db->query($sql,array($contact_type));
		}
		
	}
	
	public function insert_departments($department_arr)
	{
		$sql = 'INSERT IGNORE INTO `department`
				SET `name` = ?';
				
		foreach($department_arr as $department)
		{
			$this->db->query($sql,array($department));
		}
	}
	
	public function insert_contacts($contact_arr)
	{
	
		$locations = NULL;
		$contact_types = NULL;
		$inserted_ids = NULL;
		
		$sql = 'SELECT `id`, `name`
				FROM `contacttype`';
				
		$query = $this->db->query($sql);
		foreach($query->result() as $row)
		{
			$contact_types[$row->name]	= $row->id;
		}
		
		$sql = 'SELECT `id`,`city`
				FROM `place`
				WHERE `street` IS NULL
				AND		`streetnr`	IS NULL
				AND		`postalcode`	IS NULL
				AND		`code`IS NULL
				AND 	`room`	IS NULL';
				
		$query = $this->db->query($sql);
		foreach($query->result() as $row)
		{
			$locations[$row->city] = $row->id;
		}
		
		$sql = 'INSERT INTO `contactdata` (`email`, `surname`, `forename`, `place_id`, `contacttype_id`) values (?,?,?,?,?)
				ON DUPLICATE KEY 
				UPDATE
				`surname` = VALUES(`surname`),
				`forename` = VALUES(`forename`),
				`place_id` = VALUES(`place_id`),
				`contacttype_id` = VALUES(`contacttype_id`)';
		
		
		foreach($contact_arr as $coworker_id => $contact)
		{
			$this->db->query($sql,array($contact['E-Mail'],$contact['Name'],$contact['Vorname'],$locations[$contact['Ort PTB']],$contact_types[$contact['MaGr']]));
			$inserted_id = $this->db->insert_id();
			if($inserted_id != 0)
			{
				$inserted_ids[$coworker_id] = $inserted_id;
			}
			else
			{
				//echo $this->db->last_query();
				//exit();
				$sql_select = 'SELECT `id`
						FROM `contactdata`
						WHERE `email` = ?';
						
				$query = $this->db->query($sql_select,array($contact['E-Mail']));
				$result = $query->row();
				if($result)
				{
					$inserted_ids[$coworker_id] =  $result->id;
				}
			}
		}
		return $inserted_ids;
		
	}
	
	public function insert_coworkers($coworker_arr,$contact_ids)
	{
		$jobcluster = NULL;
		$expertgroups = NULL;
		
		
		$sql = 'SELECT `id`,`name`
				FROM `expertgroup`';
				
		$query = $this->db->query($sql);
		
		foreach($query->result() as $row)
		{
			$expertgroups[$row->name] = $row->id;
		}
		
		$sql = 'SELECT `id`,`name`
				FROM `jobcluster`';
				
		$query = $this->db->query($sql);
		
		foreach($query->result() as $row)
		{
			$jobcluster[$row->name] = $row->id;
		}

		$sql = 'INSERT INTO `employee`
				SET `id` = ?,
				`contactdata_id` = ?, 
				`costcenter_id` = ?,
				`actual_jobcluster_id` = ?, 
				`target_jobcluster_id` = ?,
				`actual_expertgroup_id` = ?,
				`target_expertgroup_id` = ?
				ON DUPLICATE KEY UPDATE 
				`id` = VALUES(`id`)';
				
				

		foreach($coworker_arr as $coworker)
		{
			$params = array($coworker['PersNr'],
										$contact_ids[$coworker['PersNr']],
										$coworker['KSt']);
			
			
			//Filter 0 Data
			if('0' != $coworker['IST-Jobcluster'])
			{
				$params[] = $jobcluster[$coworker['IST-Jobcluster']];
			}
			else
			{
				$params[] = NULL;
			}
			
			if('0' != $coworker['PLAN-Jobcluster YE'])
			{
				$params[] = $jobcluster[$coworker['PLAN-Jobcluster YE']];
			}
			else
			{
				$params[] = NULL;
			}
			
			if('0' != $coworker['IST-Expert Group'])
			{
				$params[] = $expertgroups[$coworker['IST-Expert Group']];
			}
			else
			{
				$params[] = NULL;
			}
			
			if('0' != $coworker['PLAN-Expert Group YE'])
			{
				$params[] = $expertgroups[$coworker['PLAN-Expert Group YE']];
			}
			else
			{
				$params[] = NULL;
			}
			
			
			$this->db->query($sql,$params);
		}
		
	}
	

	
	public function insert_teams($team_arr)
	{
		$sql = 'SELECT `id`, `name`
				FROM `department`';
				
		$query = $this->db->query($sql);
		
		$departments = NULL;
		
		foreach($query->result() as $row)
		{
			$departments[$row->name]	= $row->id;
		}
		
		$sql = 'INSERT IGNORE INTO `team`
				SET `name` = ?,`department_id` = ?,`costcenter_id` = ?';
				
		foreach($team_arr as $team)
		{
			$this->db->query($sql,array( $team['OrgE-E6'], $departments[$team['OrgE-E5']], $team['Costcenter']));
		}
		
		
	}
	
	public function insert_teamroles($teamrole_arr)
	{
		$teams = NULL;
		$sql = 'SELECT `id`,`name`
				FROM `team`';
		$query = $this->db->query($sql);
		
		foreach($query->result() as $row)
		{
			$teams[$row->name] = $row->id;
		}
		
		//Create the roletype if not exists
		$sql = 'SELECT `id`,`name`
				FROM `roletype`';
				
		$query = $this->db->query($sql);
		foreach($query->result() as $row)
		{
			if($row->name === 'Mitarbeiter')
			{
				$role_id = $row->id;
				break;
			}
		}
	
		if(!isset($role_id))
		{
			$sql = 'INSERT INTO `roletype` SET `name` = "Mitarbeiter"';
			$this->db->query($sql);
			$role_id = $this->db->insert_id();
		}
	
		$sql = 'INSERT INTO `employee_team`
				SET `employee_id` = ?,
				`team_id` = ?,
				`roletype_id` = ?
				ON DUPLICATE KEY UPDATE `team_id` = VALUES(`team_id`)';
	
		foreach($teamrole_arr as $teamrole)
		{
			$this->db->query($sql,array($teamrole['PersNr'],
										$teams[$teamrole['OrgE-E6']],
										$role_id));
		}

	}
	
	public function insert_comments($comment_arr)
	{
		$sql = 'INSERT INTO `note`
				SET `text` = ?, 
				`employee_id` = ?';
				
				
		foreach($comment_arr as $comment)
		{
			$this->db->query($sql,array($comment['Bemerkungen'],$comment['PersNr']));
		}
		
	}
	
	public function insert_tags($tags_arr)
	{
		$sql = 'INSERT IGNORE INTO `tag`
				SET `name` = ?';
				
		foreach($tags_arr as $tag)
		{
			$this->db->query($sql,array($tag));
		}
	}
		
	public function insert_requirements($requirement_arr)
	{
		$sql = 'SELECT `id`,`name`
				FROM `tag`';
				
		$query = $this->db->query($sql);
		$tags = NULL;
		foreach($query->result() as $row)
		{
			$tags[$row->name] = $row->id;
		}
		
		
		$sql = 'INSERT INTO `stopedemand`
				SET `title` = ?, 
				`employee_id` = ?,
				`tag_id` = ?,
				`inactive` = 0 
				ON DUPLICATE KEY UPDATE `title` = VALUES(`title`)';
				
		foreach($requirement_arr as $requirement)
		{
			$this->db->query($sql,array($requirement['Requirement'],
										$requirement['PersNr'],
										$tags[$requirement['Tag']]));
		}
		
		
	}
	
	public function map_requirement_course()
	{
		$sql = 'SELECT `stopedemand`.`id` `stopedemand_id`,`course`.`id` `course_id`
				FROM `stopedemand`, `course`
				WHERE `stopedemand`.`title` = `course`.`title`';
				
		$query = $this->db->query($sql);
		
		$sql = 'UPDATE `stopedemand`
					SET `course_id` = ?
					WHERE `id` = ?';
		
		foreach($query->result() as $row)
		{
			$this->db->query($sql,array($row->course_id,$row->stopedemand_id));			
		}
		
		
	}
	
	public function set_teamleaders($teamleader_arr)
	{
	
		//Get IDs for Teammembers
		$sql = 'SELECT cd.surname, e.id FROM contactdata cd, employee e WHERE e.contactdata_id = cd.id';
				
		$query = $this->db->query($sql);
		
		$teammembers = NULL;
		
		foreach($query->result() as $row)
		{
			$teammembers[$row->surname] = $row->id;
		}
		//var_dump($teammembers);
		//Get IDs for Teams
		$sql = 'SELECT `id`,`name`
				FROM `team`';
		$query = $this->db->query($sql);
		
		$teams = NULL;
		
		foreach($query->result() as $row)
		{
			$teams[$row->name] = $row->id;
	
		}
	
		//Check if there is a ID for the role teamleader
		$sql = 'SELECT `id` 
				FROM `roletype`
				WHERE `name` = "Teamleitung"
				LIMIT 1';
		
		$role_type_id = NULL;
		
		if($query = $this->db->query($sql))
		{
			$role_type_id = $query->row()->id;
		}
		else
		{
			$sql = 'INSERT INTO `roletype`
					SET `name` = "Teamleitung"';
			$this->db->query($sql);
			$role_type_id = $this->db->last_insert_id();
		}
		
	
	
		$sql = 'UPDATE `employee_team`
				SET `roletype_id` = '.$role_type_id.'
				WHERE `employee_id` = ?
				AND `team_id` = ?';
				
		foreach($teamleader_arr as $teamleader)
		{
			$this->db->query($sql,array($teammembers[$teamleader['Name']],$teams[$teamleader['OrgE-E6']]));
		}

		
	}
	
}